const express = require('express');
const app = express();
const http = require('http').Server(app);
const io = require('socket.io')(http);
const path = require('path');
const sqlite3 = require('sqlite3').verbose();

// 连接到 SQLite 数据库
const db = new sqlite3.Database('student_answers.db', (err) => {
    if (err) {
        console.error('数据库连接错误:', err.message);
        return;
    }
    console.log('Connected to the student_answers database.');
    // 创建学生表（修复字段名合法性，移除不必要的 UNIQUE 约束）
    db.run(`
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            student_names TEXT UNIQUE,
            answer_1 TEXT,
            answer_2 TEXT,
            answer_3 TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    `);
});

// 静态文件服务
app.use(express.static(path.join(__dirname, 'public')));

// 学生姓名查询 API（用于前端获取姓名列表）
app.get('/api/students', (req, res) => {
    db.all('SELECT student_names FROM students', (err, rows) => {
        if (err) {
            res.status(500).json({ error: '数据库查询失败' });
            return;
        }
        const allNames = rows.flatMap(row => row.student_names.split(','))
                            .map(name => name.trim())
                            .filter(name => name); // 过滤空字符串
        const uniqueNames = [...new Set(allNames)];
        res.json(uniqueNames);
    });
});

// 初始化答案统计对象
        // 模拟题目数据（题目 ID 与数据库字段对应：1→answer_1，2→answer_2）
const questions = [
    {
        id: 1,
        question: "一年有365天，我们班级里有52名同学，有同一天出生的概率有多大？",
        options: ["0%", "10%", "30%", "50%", "98%"],
        type: "choice"
    },
    {
        id: 2,
        question: "一年按照365天来算，求5个人有同一天生日的概率？（结果用小数来表示，保留3位小数）",
        type: "fill"
    },
    {
        id: 3,
        question: "一年按照365天来算，求52个人有同一天生日的概率？（结果用小数来表示，保留3位小数）",
        type: "fill"
    }
];

        // 初始化答案统计对象
        const answerStats = questions.reduce((acc, q) => {
            acc[q.id] = {};
            return acc;
        }, {});

// 初始状态
let currentQuestionIndex = 0;
let isQuestionPublished = false;

// 处理教师端连接
io.of('/teacher').on('connection', (socket) => {
    console.log('教师端已连接');
    // 立即发送当前题目和统计信息
    sendCurrentQuestionToTeacher(socket);
    // 处理教师端发送工具链接
    socket.on('sendToolLink', (data) => {
        io.of('/student').emit('receiveToolLink', data);
    });

    // 教师发布题目
    socket.on('publishQuestion', () => {
        isQuestionPublished = !isQuestionPublished;
        io.of('/student').emit('questionPublished', questions[currentQuestionIndex]);
        sendCurrentQuestionToTeacher(socket);
    });

    // 教师切换题目
    socket.on('nextQuestion', () => {
        currentQuestionIndex = (currentQuestionIndex + 1) % questions.length;
        isQuestionPublished = false;
        const currentQuestionId = questions[currentQuestionIndex].id;
        
        // 使用与sendCurrentQuestionToTeacher相同的统计逻辑
        db.all(`SELECT 
                SUM(CASE WHEN answer_${currentQuestionId} IS NULL OR answer_${currentQuestionId} = '' THEN 1 ELSE 0 END) as unanswered,
                answer_${currentQuestionId} as answer, 
                COUNT(*) as count 
                FROM students 
                GROUP BY answer_${currentQuestionId}`,
        (err, rows) => {
            if (err) return console.error(err);
            
            const stats = {};
            let unansweredCount = 0;
            
            if (rows && rows.length > 0) {
                rows.forEach(row => {
                    if (row.answer === null || row.answer === '') {
                        unansweredCount = row.unanswered;
                    } else {
                        stats[row.answer] = row.count;
                    }
                });
                
                if (unansweredCount > 0) {
                    stats['未作答'] = unansweredCount;
                }
            }
            
            io.of('/teacher').emit('answerStats', stats);
            io.of('/teacher').emit('currentQuestion', {
                question: questions[currentQuestionIndex],
                isPublished: isQuestionPublished
            });
        });
        
        // 通知学生端题目变更
        io.of('/student').emit('questionChanged', questions[currentQuestionIndex]);
    });
    // 在教师端连接处理部分添加
    socket.on('prevQuestion', () => {
        currentQuestionIndex = (currentQuestionIndex - 1 + questions.length) % questions.length;
        isQuestionPublished = false;
        const currentQuestionId = questions[currentQuestionIndex].id;
        
        // 从数据库获取该题目历史数据
        db.all(`SELECT answer_${currentQuestionId} as answer, COUNT(*) as count 
                FROM students 
                WHERE answer_${currentQuestionId} IS NOT NULL
                GROUP BY answer_${currentQuestionId}`,
        (err, rows) => {
            if (err) return console.error(err);
            
            const stats = {};
            rows.forEach(row => {
                stats[row.answer] = row.count;
            });
            
            // 同步到客户端
            io.of('/teacher').emit('answerStats', stats);
            io.of('/teacher').emit('currentQuestion', {
                question: questions[currentQuestionIndex],
                isPublished: isQuestionPublished
            });
        });
        
        // 通知学生端题目变更
        io.of('/student').emit('questionChanged', questions[currentQuestionIndex]);
    });
    // 在教师端连接处理部分添加
    socket.on('requestStats', () => {
        db.all('SELECT answer_1 FROM students', (err, rows) => {
            if (err) return console.error(err);
            const dbStats = rows.reduce((acc, row) => {
                acc[row.answer_1] = (acc[row.answer_1] || 0) + 1;
                return acc;
            }, {});
            console.log('数据库实际统计:', dbStats);
            console.log('内存统计:', answerStats[1]); // 假设当前是第一题
        });
    });
});

// 处理学生端连接
io.of('/student').on('connection', (socket) => {
    console.log('学生端已连接');
    
    // 添加恢复会话处理
    socket.on('restoreSession', (user) => {
        // 发送当前题目状态
        socket.emit('currentQuestion', {
            question: questions[currentQuestionIndex],
            isPublished: isQuestionPublished
        });
        
        // 如果题目已发布，发送题目内容
        if (isQuestionPublished) {
            socket.emit('questionPublished', questions[currentQuestionIndex]);
        }
    });

    // 学生提交答案
    socket.on('submitAnswer', (data) => {
        const { questionId, answers, user } = data;
        const studentNames = user.names;
        const answerField = `answer_${questionId}`;
    
        studentNames.forEach((studentName) => {
            db.get('SELECT * FROM students WHERE student_names =?', [studentName], (err, row) => {
                if (err) return console.error(err);
                
                const answer = answers[studentName];
                
                // 先更新数据库
                if (row) {
                    db.run(`UPDATE students SET ${answerField} =? WHERE student_names =?`, 
                    [answer, studentName], () => {
                        // 数据库更新成功后再更新内存统计
                        db.get(`SELECT COUNT(*) as count FROM students WHERE ${answerField} =?`, 
                        [answer], (err, result) => {
                            if (!err) {
                                answerStats[questionId][answer] = result.count;
                                io.of('/teacher').emit('answerStats', answerStats[questionId]);
                            }
                        });
                    });
                } else {
                    db.run(`INSERT INTO students (student_names, ${answerField}) VALUES (?,?)`, 
                    [answer, studentName], () => {
                        // 数据库插入成功后再更新内存统计
                        answerStats[questionId][answer] = 1;
                        io.of('/teacher').emit('answerStats', answerStats[questionId]);
                    });
                }
            });
        });
    });
});

// 辅助函数：发送当前题目给教师端
function sendCurrentQuestionToTeacher(socket) {
    const currentQuestionId = questions[currentQuestionIndex].id;
    
    // 从数据库获取最新统计数据
    db.all(`SELECT 
            SUM(CASE WHEN answer_${currentQuestionId} IS NULL OR answer_${currentQuestionId} = '' THEN 1 ELSE 0 END) as unanswered,
            answer_${currentQuestionId} as answer, 
            COUNT(*) as count 
            FROM students 
            GROUP BY answer_${currentQuestionId}`,
    (err, rows) => {
        if (err) {
            console.error('数据库查询错误:', err);
            return socket.emit('currentQuestion', {
                question: questions[currentQuestionIndex],
                isPublished: isQuestionPublished,
                stats: {}
            });
        }
        
        const stats = {};
        let unansweredCount = 0;
        
        if (rows && rows.length > 0) {
            rows.forEach(row => {
                if (row.answer === null || row.answer === '') {
                    unansweredCount = row.unanswered;
                } else {
                    stats[row.answer] = row.count;
                }
            });
            
            // 添加未作答统计
            if (unansweredCount > 0) {
                stats['未作答'] = unansweredCount;
            }
        }
        
        socket.emit('currentQuestion', {
            question: questions[currentQuestionIndex],
            isPublished: isQuestionPublished,
            stats: stats
        });
    });
}

const port = 3000;
http.listen(port, () => {
    console.log(`服务器运行在 http://localhost:${port}`);
});